package com.tim.helper.analysis.shp;

import com.alibaba.fastjson.JSON;
import com.tim.ArgConstant;
import com.tim.helper.CountRuntime;
import com.tim.third.YmlUtils;
import com.tim.util.CmdAndShUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.maven.model.Model;
import org.apache.maven.model.Plugin;
import org.apache.maven.model.io.xpp3.MavenXpp3Reader;
import org.apache.maven.model.io.xpp3.MavenXpp3Writer;
import org.codehaus.plexus.util.xml.Xpp3Dom;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.*;
import java.nio.file.attribute.BasicFileAttributes;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 数据库表结构导出插件
 *  https://gitee.com/leshalv/screw
 *  show databases  查看全部数据库
 *  select VERSION()  查看数据库版本，修改pom依赖版本
 */
public class ShpSrewPluginHelper extends CountRuntime {
    // 中台 店绩
    String project = "中台";
    // 数据库信息；before方法中按project设置
    String username,password,dbname,ipPortDb;

    // 以下一般不改
    List<String> dbList = null;
    List<String> excludeIdNames = Arrays.asList(("create_user_id,modify_user_id," +
            "province_id,city_id,area_id").split(","));
    List<String> includeIdNames = Arrays.asList(("merchant_id,shop_id,warehouse_id,goods_id,sku_id,activity_id,company_id,message_id,order_id,ad_page_id").split(","));
    String projectDir = System.getProperty("user.dir");
    StringBuilder tableNameBuilder = new StringBuilder("DB:\n  TABLE: TABLE_NAME；表描述\n");

    @Test
    public void multiDb() throws Exception {
        String excludes = "information_schema,mysql,test,sys,__recycle_bin__,performance_schema," +
                "test1";
            List<String> excludeList = Arrays.asList(excludes.split(","));
        dbList = dbList.stream()
                    .filter(e->!excludeList.contains(e))
                    .filter(StringUtils::isNotBlank)
                    .collect(Collectors.toList());

        for(String db : dbList){
            dbname = db;
            ipPortDb = ipPortDb.substring(0, ipPortDb.indexOf("/"))+"/"+db;
            singleDb();
            // 移动到对应文件夹
            File destDir = Paths.get(projectDir, "doc", project).toFile();
            if(!destDir.exists()){
                destDir.mkdirs();
            }
            Files.move(Paths.get(projectDir, "doc", dbname+".html"),
                    Paths.get(projectDir, "doc", project, dbname+".html"),
                    StandardCopyOption.REPLACE_EXISTING);
        }
    }

    @Test
    public void singleDb() throws Exception {

        String jdbcUrl = String.format("jdbc:mysql://%s?useSSL=false", ipPortDb);
//        String driverClassName = "com.mysql.jdbc.Driver";// mysql 5
    String driverClassName = "com.mysql.cj.jdbc.Driver";// mysql 8
        tableNameBuilder.append(dbname+": \n");

        /* step1，修改pom插件配置，运行插件*/
        String pomPath = projectDir+"/pom.xml";
        MavenXpp3Reader POM_READER = new MavenXpp3Reader();
        Model pomModel = POM_READER.read(new FileReader(pomPath));
        List<Plugin> plugins = pomModel.getBuild().getPlugins();
        boolean returnFlag = true;
        for(Plugin plugin : plugins){
            if(plugin.getArtifactId().equals("screw-maven-plugin")){
                returnFlag = false;
                Xpp3Dom configuration = (Xpp3Dom) plugin.getConfiguration();
                configuration.getChild("jdbcUrl").setValue(jdbcUrl);
                configuration.getChild("username").setValue(username);
                configuration.getChild("password").setValue(password);
                configuration.getChild("driverClassName").setValue(driverClassName);
                configuration.getChild("fileName").setValue(dbname);
                configuration.getChild("openOutputDir").setValue("true");
                configuration.getChild("title").setValue(dbname +"文档");
                configuration.getChild("description").setValue(dbname +"文档生成");
                break;
            }
        }
        if(returnFlag){
            return;
        }
        MavenXpp3Writer POM_WRITER = new MavenXpp3Writer();
        POM_WRITER.write(new FileWriter(pomPath), pomModel);

        // 运行插件，获取命令
        String cmd = System.getProperty("user.dir").replace(":", ": && cd ")+" && mvn screw:run";
        CmdAndShUtil.runBat(cmd);
        /* step2，读取、修改html*/
        handleHtml(dbname);
    }

    @Test// 处理html，输出关联id
    public void multiProjectHtml() throws Exception {
        // 修改project；可为空

        List<String> dbList = new ArrayList<>();
        Files.walkFileTree(Paths.get(projectDir, "doc", project), new SimpleFileVisitor<Path>(){
            @Override
            public FileVisitResult visitFile(Path path, BasicFileAttributes attrs)
                    throws IOException
            {
                Objects.requireNonNull(path);
                Objects.requireNonNull(attrs);
                String fileName = path.getFileName().toString();
                if(fileName.endsWith(".html")){
                    dbList.add(fileName.substring(0, fileName.length()-5));
                }
                return FileVisitResult.CONTINUE;
            }
        });
        for(String db : dbList){
            handleHtml(db, project);
        }
    }

    @Test
    public void singleProjectHtml() throws Exception {
        List<String> dbList = Arrays.asList("xqxc_xiudou".split(","));
        for(String db : dbList){
            handleHtml(db, project);
        }
    }

    @Test// 输出全部的表关联；json格式
    public void printTableRefJson() throws Exception {
        // 修改project；可为空
        String coreColumns = "goods_id,sku_id";// 逗号隔开

        String fileName = "tableRef.yml";
        Map<String, Object> refMap = YmlUtils.loadYml(ymlPath + fileName);

        /* step1，refMap转Map<String, Map<String,String>>，db.table-columnRefTableMap*/
        Map<String, Map<String,Object>> dbTable_columnRefMap = new HashMap<>();
        Map<String, Set<String>> tableRefTableMap =new HashMap<>();
        for(String dbName : refMap.keySet()){
            Map<String, Map<String, Object>> dbTableRefMap = (Map<String, Map<String, Object>>) getMapValueOrEmpty(refMap,dbName,1);
            if(MapUtils.isEmpty(dbTableRefMap)){
                continue;
            }
            for(String table : dbTableRefMap.keySet()){
                Map<String, Object> columnRefTableMap = (Map<String, Object>) getMapValueOrEmpty(dbTableRefMap,table,1);
                dbTable_columnRefMap.put(dbName+"."+table, columnRefTableMap);
                if(MapUtils.isEmpty(columnRefTableMap)){
                    tableRefTableMap.put(dbName+"."+table, Collections.emptySet());
                    continue;
                }
                Set<String> refTableSet = columnRefTableMap.values().stream()
                        .map(Objects::toString)
                        .filter(StringUtils::isNotBlank)
                        .collect(Collectors.toSet());
                tableRefTableMap.put(dbName+"."+table, refTableSet);
            }
        }

        /* step2，构建树状结构；循环关联？*/
        // 获取有循环关联的表
        List<List<String>> cyclic = getCyclic(tableRefTableMap);
        Set<String> notRootSet = new HashSet<>();
        Map<String, Object> resultTreeMap = new LinkedHashMap<>();// 保证输出顺序
        List<String> coreColumnList = Arrays.asList(coreColumns.split(","));
//        resultTreeMap.put("cyclic", cyclic);// 考虑去重
        for(String dbTable : dbTable_columnRefMap.keySet()){
            Map<String, Object> columnRefTableMap = (Map<String, Object>) getMapValueOrEmpty(dbTable_columnRefMap,dbTable,1);
            resultTreeMap.put(dbTable, columnRefTableMap);
            if(MapUtils.isEmpty(columnRefTableMap)){
                continue;
            }
            // columnRefTableMap，字符串引用替换为对象引用
            for(String column : columnRefTableMap.keySet()){
//                System.out.println(dbTable+"."+column);
                Object refDbTable = getMapValueOrEmpty(columnRefTableMap,column,2);
                if("".equals(refDbTable)){
                    continue;
                }
                notRootSet.add(refDbTable.toString());
                HashMap<Object, Object> value = new HashMap<>();
                value.put(refDbTable, dbTable_columnRefMap.containsKey(refDbTable) ? dbTable_columnRefMap.get(refDbTable) : refDbTable);
                columnRefTableMap.put(column, value );
            }
        }
        // 移除无循环关联的非根
        Set<String> cyclicSet = cyclic.stream().flatMap(List::stream).collect(Collectors.toSet());
        for(String item : notRootSet){
            if(!cyclicSet.contains(item))
                resultTreeMap.remove(item);
        }
        // 普通toString，会因循环关联报错；转json
        Map<String, String> resultStrMap = resultTreeMap.keySet()
                .stream()
                .collect(Collectors.toMap(Function.identity(), e->JSON.toJSONString(resultTreeMap.get(e))));
        Map<String, Object> resultMap = resultTreeMap.keySet().stream()
                .filter(e->!MapUtils.isEmpty((Map) resultTreeMap.get(e)))
                .filter(e->coreColumnList.isEmpty() || coreColumnList.stream().anyMatch(t->resultStrMap.get(e).contains(t)))
                .collect(Collectors.toMap(Function.identity(), resultTreeMap::get));
        String jsonStr = JSON.toJSONString(resultMap)
                .replaceAll("\\\\", "")
                .replaceAll("\\:null\\}", ":\"null\"}");
        System.out.println(jsonStr);
    }

    @Test// 输出标准关联表的id
    public void printIncludeIds(){
        String includeIds = ymlColumnTableStandardRefMap.keySet().stream()
                .filter(StringUtils::isNotBlank)
                .filter(e -> !"COLUMN".equals(e))
                .collect(Collectors.joining(","));
        System.out.println(includeIds);
    }

    StringBuilder tableRefBuilder =
            new StringBuilder("DB:\n  TABLE:\n    COLUMN: DB_NAME.REF_TABLE；$ref为循环路径\n");
    // jsoup处理，写回
    private void handleHtml(String fileName, String... dirs) throws Exception {
        /* step1，读取文档；添加class标记；jsoup解析文档*/
        String fileFullName = fileName.endsWith(".html") ? fileName : fileName+".html";
        fileName = fileName.replace(".html", "");
        List<String> pathList = new ArrayList<>();
        pathList.add("doc");
        pathList.addAll(Arrays.asList(dirs));// 多文件处理，只能在doc目录
        pathList.add(fileFullName);
        Path filePath = Paths.get(projectDir, pathList.toArray(new String[0]));
        String content = Files.readAllLines(filePath).stream().
                collect(Collectors.joining("\n"));
        // 添加class标记；jsoup解析文档
        String cssClassName = "table_name";
        content = content.replaceAll("(margin-top:30px;[\"'])", "$1 class=\""+cssClassName+"\""); // 添加样式，便于选择
        Document document = Jsoup.parse(content);
        Element body = document.body();
        Elements tables = body.select("table");
        // 读取名称、关联
        Map<String, Object> tableNameMap = (Map<String, Object>) getMapValueOrEmpty(ymlTableNameMap, fileName,1);
        Map<String, Object> tableRefMap = (Map<String, Object>) getMapValueOrEmpty(ymlTableRefMap, fileName, 1);

        /* step2，处理表描述缺失*/
        Element indexTable = tables.get(0);// 目录表
        for(Element tr : indexTable.select("tr")){
            Element child = tr.child(2);// 说明列
            if(StringUtils.isBlank(child.text())){
                String tableName = tr.child(1).child(0).text();
                String tableNameStr = getMapValueOrEmpty(tableNameMap, tableName, 2, tableName).toString();
                child.text(tableNameStr);
                tableNameBuilder.append(String.format("  %s: %s\n", tableName, tableNameStr));
            }
        }

        Elements divs = body.select("div."+cssClassName);
        for(Element div : divs){
            String tableName = div.child(1).text().split("：")[1];
            if(tableNameMap.containsKey(tableName)){
                Element nameEle = div.nextElementSibling();
                nameEle.html("说明："+tableNameMap.getOrDefault(tableName, tableName));
            }
        }
        /* step3，处理表关联*/
        String ymlDBLine = fileName+": ";
        tableRefBuilder.append(ymlDBLine).append("\n");
        System.out.println(ymlDBLine);
        for (int i = 1; i < tables.size(); i++){
            Element table = tables.get(i);
            Element select = table.previousElementSibling()
                    .previousElementSibling()
                    .previousElementSibling()
                    .child(1);
            String tableName = select.text().replaceAll("表名：", "");
            String ymlTableLine = String.format("  %s: ", tableName);
            tableRefBuilder.append(ymlTableLine).append("\n");
            System.out.println(ymlTableLine);
            Map<String, String> columnTableRefMap = (Map<String, String>)getMapValueOrEmpty(tableRefMap, tableName, 1);
            for(Element tr : table.select("tr")){
                String column = tr.child(1).text();
                if(column.endsWith("_id") && !excludeIdNames.contains(column)){
                    String refTable = (String) getMapValueOrEmpty(columnTableRefMap, column, 2);
                    if(StringUtils.isBlank(refTable)){
                        // 尝试从参考关联中获取值
                        refTable = (String) getMapValueOrEmpty(ymlColumnTableStandardRefMap, column, 2);
                    }
                    if(includeIdNames.contains(column)){
                        String ymlColumnLine = String.format("    %s: %s", column, refTable);
                        tableRefBuilder.append(ymlColumnLine).append("\n");
                        System.out.println(ymlColumnLine);
                    }
                    Element descCol = tr.child(8);
                    descCol.text(descCol.text()+"；"+ refTable);
                }
            }
        }

        Files.write(filePath, Arrays.asList(document.toString()));
    }
    // type：1Map，2String，3
    private Object getMapValueOrEmpty(Map map, Object key, int type, Object... defaultValues){
        Object value = map.get(key);
        if(value!=null){
            return value;
        }else if(defaultValues!=null && defaultValues.length>0){
            return defaultValues[0];
        }
        return type==1 ? Collections.emptyMap()
                : type==2 ? ""
                : new Object();
    }

    // 找出循环依赖链
    private List<List<String>> getCyclic(Map<String, Set<String>> tableRefTableMap){
        List<List<String>> cyclicTables = new ArrayList<>();
        for(String table : tableRefTableMap.keySet()){
            List<String> preTables = new ArrayList<>();
            preTables.add(table);
            doCalCyclic(tableRefTableMap, preTables, cyclicTables);
        }
        return cyclicTables;
    }

    private void doCalCyclic(Map<String, Set<String>> tableRefTableMap, List<String> preTables, List<List<String>> cyclicTables) {
        String table = preTables.get(preTables.size() - 1);
        Set<String> columnRefTableSet = tableRefTableMap.get(table);
        if(CollectionUtils.isEmpty(columnRefTableSet)){
            return;
        }
        for(String refTable : columnRefTableSet){
            if(preTables.contains(refTable)){
                // 循环关联
                List<String> cyclicList = new ArrayList<>(preTables.subList(preTables.indexOf(refTable), preTables.size()));
                cyclicList.add(refTable);
                cyclicTables.add(cyclicList);
                continue;
            }
            List<String> newPreTables = new ArrayList<>(preTables);// 新对象，防干扰
            newPreTables.add(refTable);
            doCalCyclic(tableRefTableMap, newPreTables, cyclicTables);
        }
    }

    String ymlPath = projectDir+"/src\\main\\resources\\maven\\screw/"+project+"/";

    Map<String, Object> ymlTableNameMap, ymlTableRefMap, ymlColumnTableStandardRefMap;
    @Before
    public void before() throws Exception {
        // 读取名称、关联
        ymlTableNameMap = YmlUtils.loadYml(ymlPath + "tableName.yml");
        ymlTableRefMap = YmlUtils.loadYml(ymlPath + "tableRef.yml");
        ymlColumnTableStandardRefMap = YmlUtils.loadYml(ymlPath + "columnTableStandardRef.yml");
        // 中台 店绩
        if("中台".equals(project)){
            userPlatformDb();
        }else if("店绩".equals(project)){
            userMallDb();
        }else{
            throw new RuntimeException("project 参数不正确");
        }

    }

    private void userPlatformDb(){
        username = "root";
        password = "shpDb@2022";
        dbList = Arrays.asList( ("antigen,gift_redemption,information_schema," +
                "jira,lessee,medical_meterial,mysql,nacos-config," +
                "performance_schema,seata,seata142,shp-authorization," +
                "shp-bluetooth,shp-essbasic,shp-hipp,shp-job,shp-mall," +
                "shp-mm,shp-pay,shp-payment-center,shp-product," +
                "shp-smart-hospital,shp-sop,shp-sp,shp-system,shp-system-bak," +
                "shp-wecom,sp-bak,sys,test1,third-open,user_00,wxpayment_00")
                .split(","));
        dbname = "antigen";
        ipPortDb = "162.14.204.178:13336/"+dbname;
    }

    private void userMallDb(){
        username = "dev_admin";
        password = "Dev+2019@lazypig";
        dbList = Arrays.asList(("pigear_biz,pigear_order," +
                "pigear_settle_pay,pigear_ele,pigear_xcron").split(","));
        dbname = "dev_admin";
        ipPortDb = "rm-bp158x381519cc14qjo.mysql.rds.aliyuncs.com:3306/"+dbname;

    }

    @After
    public void after() throws Exception {
        Files.write(Paths.get(ymlPath, "tempTableRef.yml"), Arrays.asList(tableRefBuilder.toString().split("\n")));
        Files.write(Paths.get(ymlPath, "tempTableName.yml"), Arrays.asList(tableNameBuilder.toString().split("\n")));

    }

    @Test
    public void printTableHref(){
        // 中台 店绩
        String dic = "中台";
        String destDir = ArgConstant.PROJECT_PATH+"doc/"+dic;
        String[] list = new File(destDir).list();
        String lineTemplate = "<li><a href=\"./%1$s/%2$s.html\" target=\"_blank\">%2$s</a></li>";
        Arrays.stream(list)
                .filter(StringUtils::isNotBlank)
                .forEach(
                        e -> {
                            System.out.println(String.format(lineTemplate, dic, e.substring(0, e.length()-5)));
                        });
    }
}
